package db;

import beans.B_CriterioAuditoria;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import beans.B_DominioAuditoria;
import conexion.Conexion;
import java.sql.Types;

/**
 *
 * @author JoseHernan
 */
public class DB_DominioAuditoria {

    public static ArrayList<B_DominioAuditoria> getListByIdCriterioAuditoria(int idCriterioAuditoria) {
        ArrayList<B_DominioAuditoria> list = new ArrayList<B_DominioAuditoria>();
        try {
            Connection con = Conexion.getConexion();

            PreparedStatement ps;
            ResultSet rs;
    
            ps = con.prepareStatement(" select id, codigo, nombre from tbl_dominio_auditoria where removed = false and id_criterio_auditoria = " + idCriterioAuditoria + " order by CAST(SUBSTRING(codigo, 3) AS SIGNED INTEGER) ");

            rs = ps.executeQuery();
            B_DominioAuditoria obj;
            while (rs.next()) {
                obj = new B_DominioAuditoria();
                //Asign values
                obj.setId(rs.getInt("id"));
                obj.setCodigo(rs.getString("codigo"));
                obj.setNombre(rs.getString("nombre"));
                //Add to List
                list.add(obj);
            }
            rs.close();
            ps.close();
            con.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return list;
    }
    
    public static ArrayList<B_DominioAuditoria> getList(int id) {
        ArrayList<B_DominioAuditoria> list = new ArrayList<B_DominioAuditoria>();
        try {
            Connection con = Conexion.getConexion();

            PreparedStatement ps;
            ResultSet rs;
            if (id == 0) {
                ps = con.prepareStatement(" SELECT d.id, d.codigo, d.nombre, d.id_criterio_auditoria, c.nombre as criterio_auditoria FROM tbl_dominio_auditoria d inner join tbl_criterio_auditoria c on c.id = d.id_criterio_auditoria where d.removed = false order by CAST(SUBSTRING(d.codigo, 3) AS SIGNED INTEGER) ");
            } else {
                ps = con.prepareStatement(" SELECT d.id, d.codigo, d.nombre, d.id_criterio_auditoria, c.nombre as criterio_auditoria FROM tbl_dominio_auditoria d inner join tbl_criterio_auditoria c on c.id = d.id_criterio_auditoria where d.removed = false and d.id = " + id);
            }
            rs = ps.executeQuery();
            B_DominioAuditoria obj;
            B_CriterioAuditoria objCriterioAuditoria;
            while (rs.next()) {
                obj = new B_DominioAuditoria();
                //Asign values
                obj.setId(rs.getInt("id"));
                obj.setCodigo(rs.getString("codigo"));
                obj.setNombre(rs.getString("nombre"));
                objCriterioAuditoria = new B_CriterioAuditoria();
                objCriterioAuditoria.setId(rs.getInt("id_criterio_auditoria"));
                objCriterioAuditoria.setNombre(rs.getString("criterio_auditoria"));
                obj.setObjCriterioAuditoria(objCriterioAuditoria);
                //Add to List
                list.add(obj);
            }
            rs.close();
            ps.close();
            con.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return list;
    }

    //SP CRUD
    public static String crud(B_DominioAuditoria obj, String action) {
        String result = "";
        CallableStatement cs = null;
        Connection con = Conexion.getConexion();
        try {
            cs = con.prepareCall("{call sp_crud_dominio_auditoria(?,?,?,?,?,?,?)}");

            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(2, action);
            cs.setInt(3, obj.getId());
            cs.setString(4, obj.getCodigo());
            cs.setString(5, obj.getNombre());
            cs.setInt(6, obj.getObjCriterioAuditoria().getId());
            cs.setString(7, obj.getUser());
            cs.executeUpdate();
            result = cs.getString(1);
        } catch (Exception ex) {
            result = ex.getMessage();
        } finally {
            try {
                cs.close();
                con.close();
            } catch (Exception ex) {
                System.out.println(ex);
            }
        }
        return result;
    }
}
